package com.bloom.product.qms.sync;

import com.alibaba.fastjson.JSON;
import com.bloom.cloud.common.context.BaseContextHandler;
import com.bloom.cloud.core.db.DynamicDataSourceHolder;
import com.bloom.cloud.processor.common.annotation.Executor;
import com.bloom.cloud.processor.common.annotation.ServiceExecutor;
import com.bloom.cloud.processor.common.model.TableColumnProps;
import com.bloom.cloud.processor.common.model.TsBusinessDefinition;
import com.bloom.cloud.processor.common.params.ServiceDefinitionParam;
import com.bloom.cloud.processor.common.params.ServiceInParam;
import com.bloom.cloud.processor.common.params.ServiceOutParam;
import com.bloom.cloud.processor.common.service.DynamicBaseExecutor;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import java.net.URL;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导入售后记录数据
 */
@ServiceExecutor(code = "QMS_DYNAMIC_SALE_RECORD_MQP_IMPORT")
public class QMSSaleRecordImportExecutor extends DynamicBaseExecutor {

    private static final Map<String, String> dayMapReversal = new HashMap<>();
    private static final Map<String, String> monthMapReversal = new HashMap<>();
    static {
        dayMapReversal.put("1", "01");
        dayMapReversal.put("2", "02");
        dayMapReversal.put("3", "03");
        dayMapReversal.put("4", "04");
        dayMapReversal.put("5", "05");
        dayMapReversal.put("6", "06");
        dayMapReversal.put("7", "07");
        dayMapReversal.put("8", "08");
        dayMapReversal.put("9", "09");
        dayMapReversal.put("A", "10");
        dayMapReversal.put("B", "11");
        dayMapReversal.put("C", "12");
        dayMapReversal.put("D", "13");
        dayMapReversal.put("E", "14");
        dayMapReversal.put("F", "15");
        dayMapReversal.put("G", "16");
        dayMapReversal.put("H", "17");
        dayMapReversal.put("J", "18");
        dayMapReversal.put("K", "19");
        dayMapReversal.put("L", "20");
        dayMapReversal.put("M", "21");
        dayMapReversal.put("N", "22");
        dayMapReversal.put("P", "23");
        dayMapReversal.put("Q", "24");
        dayMapReversal.put("R", "25");
        dayMapReversal.put("S", "26");
        dayMapReversal.put("T", "27");
        dayMapReversal.put("U", "28");
        dayMapReversal.put("X", "29");
        dayMapReversal.put("Y", "30");
        dayMapReversal.put("Z", "31");

        monthMapReversal.put("1", "01");
        monthMapReversal.put("2", "02");
        monthMapReversal.put("3", "03");
        monthMapReversal.put("4", "04");
        monthMapReversal.put("5", "05");
        monthMapReversal.put("6", "06");
        monthMapReversal.put("7", "07");
        monthMapReversal.put("8", "08");
        monthMapReversal.put("9", "09");
        monthMapReversal.put("X", "10");
        monthMapReversal.put("Y", "11");
        monthMapReversal.put("Z", "12");
    }

    public static String convertBatchNumberToDate(String batchNumber) {
        String regex = "^[0-9][1-9X-Z][1-9A-Z]$";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(batchNumber);

        if (!matcher.matches()) {
            return null;
        }
        String yearPart = batchNumber.substring(0, 1);
        String monthPart = batchNumber.substring(1, 2);
        String dayPart = batchNumber.substring(2, 3);

        // Check if yearPart is a digit
        if (!Character.isDigit(yearPart.charAt(0))) {
            return null;
        }
        int currentYear = java.time.Year.now().getValue();
        int decadeStartYear = (currentYear / 10) * 10;
        int targetYear = decadeStartYear + Integer.parseInt(yearPart);
        if (targetYear > currentYear) {
            targetYear -= 10;
        }
        String month = monthMapReversal.getOrDefault(monthPart, "00");
        String day = dayMapReversal.getOrDefault(dayPart, "00");
        if ("00".equals(month) || "00".equals(day)) {
            return null;
        }
        return String.format("%d-%s-%s", targetYear, month, day);
    }

    private String getFileUrl(String fileId,String system){
        DynamicDataSourceHolder.setDbType(system);
        StringBuffer sql = new StringBuffer();
        sql.append(" select url from ts_attachment where  id =  "+fileId);
        List<Map<String, Object>> infoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        String url = "";
        if(infoList != null && infoList.size() > 0){
            url = infoList.get(0).get("url")+"";
        }
        return url;
    }

    @Executor
    @Transactional
    public ServiceOutParam execute(ServiceDefinitionParam param) {
        String system = param.getInParam().getInstance();
        ServiceInParam inParam = param.getInParam();
        Assert.notNull(inParam.get("file"), "参数file不能为空.");

        String file = String.valueOf(inParam.get("file"));

        String fileUrl =  getFileUrl(file,system);
       
        String instance = "qms";
        final String TT_SALE_RECORD_QMS = "TT_SALE_RECORD_QMS";
        TsBusinessDefinition saleRecordDefinition = JSON.parseObject(super.configLoadService.getBusinessDefinitionByTableName(TT_SALE_RECORD_QMS, instance), TsBusinessDefinition.class);
        TableColumnProps saleRecordColumnProps = JSON.parseObject(super.configLoadService.getTableColumnPropsByTableName(TT_SALE_RECORD_QMS, instance), TableColumnProps.class);
        // 字典
        Map<String,String> entryMap = getDictEntryByTypeCodes(system,"'MQP_BASE'");

        try {
            URL url = new URL(fileUrl);
            InputStream inputStream = url.openStream();
            XSSFWorkbook workBook = getWorkbook(inputStream, "a.xlsx");
            if (null == workBook){
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("hasError", true);
                resultMap.put("errorInfos", "文件异常,请检查后重新上传Excel文件");
                HashMap<String, Object> params = new HashMap<>();
                params.put("returnData", resultMap);
                return ServiceOutParam.success(params);
            }
            XSSFSheet sheet = workBook.getSheetAt(0);
            if (sheet == null){
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("hasError", true);
                resultMap.put("errorInfos", "上传的Excel文件为空,请重新上传!!");
                HashMap<String, Object> params = new HashMap<>();
                params.put("returnData", resultMap);
                return ServiceOutParam.success(params);
            }
            int maxRow = sheet.getLastRowNum();
            if (maxRow < 1){
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("hasError", true);
                resultMap.put("errorInfos", "未检测到Excel表格中有数据！");
                HashMap<String, Object> params = new HashMap<>();
                params.put("returnData", resultMap);
                return ServiceOutParam.success(params);
            }

            // 获取主机厂信息
            Map<String,Map<String, Object>> mainEnginePlantMap = getMainEnginePlantInfo(system);
            // 获取标准总成表信息
            Map<String,Map<String, Object>> standardAssemblyMap = getStandardAssemblyInfo(system);
            // 获取初步分析结果信息
            Map<String,Map<String, Object>> mqpDefectMap = getMqpDefectInfo(system);

            int judgeSize = 50; // 数据表格的列数（字段数）
            String value = null;
            Row row = null;
            int totalInt = 0;

            Map<Integer, Map<String,Object>> saleRecordAddMap = new HashMap<>();
            Map<Integer, Map<String,Object>> saleRecordUpdateMap = new HashMap<>();
            final List<String> errorInfos = new ArrayList<String>();

            for (int i = 1; i <= maxRow; i++){
                try {
                    Map<String,Object> saleRecordMap = new HashMap<>();
                    row = sheet.getRow(i); // 获得行
                    if (row == null) {
                        continue; // 跳过空行
                    }

                    int index = 0;// 第一列
                    try {
                        // 第1列的值 - ID (有值表示修改，空值表示新增)
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            // 整行为空时，导入操作中断
                            if (isAllLineBlank(row, judgeSize)) {
                                break;
                            }
                            // ID为空，标记为新增
                        } else {
                            // ID有值，标记为修改
                            saleRecordMap.put("id", value);
                        }

                        // 计算导入成功总行数
                        totalInt++;

                        // 第2列的值 - 编号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("code", value);

                        // 第3列的值 - 零件简称（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列无需处理

                        // 第4列的值 - 产品名称（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列无需处理

                        // 第5列的值 - 筛选时间
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[筛选时间]，错误信息->[筛选时间不能为空]<br/>");
                            continue;
                        }

                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date screenTime = sdf.parse(value);
                                saleRecordMap.put("back_month", screenTime);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[筛选时间]，错误信息->[筛选时间格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第6列的值 - 主机出库日期
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date outStockDate = sdf.parse(value);
                                saleRecordMap.put("mark_time", outStockDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[主机出库日期]，错误信息->[主机出库日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第7列的值 - 生产基地
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[生产基地]，错误信息->[生产基地不能为空]<br/>");
                            continue;
                        }

                        // 将基地名称转换为基地编码
                        String baseValue = value;
                        if (StringUtils.isNotBlank(baseValue)) {
                            Map<String, String> baseEntryMap = getDictEntryByTypeCodes(system, "'MQP_BASE'");
                            String baseCode = null;
                            for (Map.Entry<String, String> entry : baseEntryMap.entrySet()) {
                                if (baseValue.equals(entry.getValue())) {
                                    baseCode = entry.getKey();
                                    break;
                                }
                            }

                            if (baseCode == null) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[生产基地]，错误信息->[生产基地" + baseValue + "不存在]<br/>");
                                continue;
                            }
                            saleRecordMap.put("base", baseCode);
                        }

                        // 第8列的值 - 主机厂名称
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[主机厂名称]，错误信息->[主机厂名称不能为空]<br/>");
                            continue;
                        }

                        if (StringUtils.isNotBlank(value)) {
                            if (!mainEnginePlantMap.containsKey(value)) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[主机厂名称]，错误信息->[主机厂不存在]<br/>");
                                continue;
                            }
                            Map<String,Object> mainEnginePlantValue = mainEnginePlantMap.get(value);
                            saleRecordMap.put("customer_id", mainEnginePlantValue.get("id"));
                        }

                        // 第9列的值 - 车架号
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[车架号]，错误信息->[车架号不能为空]<br/>");
                            continue;
                        }
                        saleRecordMap.put("vim", value);

                        // 第10列的值 - 行驶里程（KM）
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("drive_distance", value);

                        // 第11列的值 - 批次号
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[批次号]，错误信息->[批次号不能为空]<br/>");
                            continue;
                        }
                        saleRecordMap.put("batch_code", value);

                        // 第12列的值 - 灯具制造日期
                        index++;
                        value = getCell(row, index);
                        String madeTimeValue = value;
                        if (StringUtils.isBlank(madeTimeValue)) {
                            // 如果灯具制造日期为空,尝试从批次号转换
                            String batchNo = (String) saleRecordMap.get("batch_code");
                            if (StringUtils.isNotBlank(batchNo) && batchNo.length() == 3) {
                                String convertedDate = convertBatchNumberToDate(batchNo);
                                if (convertedDate != null) {
                                    try {
                                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                        Date manufactureDate = sdf.parse(convertedDate);
                                        saleRecordMap.put("made_time", manufactureDate);
                                    } catch (ParseException e) {
                                        errorInfos.add("第" + (i + 1) + "行导入失败，错误信息->[批次号转换为日期格式错误]<br/>");
                                        continue;
                                    }
                                }
                            }
                        } else {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date manufactureDate = sdf.parse(madeTimeValue);
                                saleRecordMap.put("made_time", manufactureDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[灯具制造日期]，错误信息->[灯具制造日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第13列的值 - 用户描述（全字段）
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("consumer_describe_all", value);

                        // 第14列的值 - 用户简述
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("consumer_describe_extract", value);

                        // 第15列的值 - 初步分析结果
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            if (!mqpDefectMap.containsKey(value)) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[初步分析结果]，错误信息->[初步分析结果不存在]<br/>");
                                continue;
                            }
                            Map<String,Object> mqpDefectValue = mqpDefectMap.get(value);
                            saleRecordMap.put("defect_id", mqpDefectValue.get("id"));
                        }

                        // 第16列的值 - 缺陷代码（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列无需处理

                        // 第17列的值 - 备注
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("remark", value);

                        // 第18列的值 - 最终分析结果
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("last_analyse", value);

                        // 第19列的值 - 责任方
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("responsibility_person", value);

                        // 第20列的值 - 唯一号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("unique_code", value);

                        // 第21列的值 - 换下零件号
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[换下零件号]，错误信息->[换下零件号不能为空]<br/>");
                            continue;
                        }
                        saleRecordMap.put("up_part_code", value);

                        // 第22列的值 - 换上零件号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("down_part_code", value);

                        // 第23列的值 - 产品编码
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isBlank(value)) {
                            errorInfos.add("第" + (i + 1) + "行导入失败，列[产品编码]，错误信息->[产品编码不能为空]<br/>");
                            continue;
                        }

                        if (StringUtils.isNotBlank(value)) {
                            if (!standardAssemblyMap.containsKey(value)) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[产品编码]，错误信息->[标准总成不存在]<br/>");
                                continue;
                            }
                            Map<String,Object> standardAssemblyValue = standardAssemblyMap.get(value);
                            saleRecordMap.put("material_id", standardAssemblyValue.get("id"));
                        }

                        // 第24列的值 - MES零件号编码
                        index++;
                        value = getCell(row, index);
                        // MES零件号编码为冗余列，不需要处理

                        // 第25列的值 - 返修站代码
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("repair_station_code", value);

                        // 第26列的值 - 返修站名称
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("repair_station_name", value);

                        // 第27列的值 - 索赔申请号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("claim_app_code", value);

                        // 第28列的值 - 委托书号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("entrust_code", value);

                        // 第29列的值 - 入库编号
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("store_code", value);

                        // 第30列的值 - 索赔周次
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("claim_week", value);

                        // 第31列的值 - 购车日期
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date buyDate = sdf.parse(value);
                                saleRecordMap.put("buy_date", buyDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[购车日期]，错误信息->[购车日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第32列的值 - 返修日期
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date repairDate = sdf.parse(value);
                                saleRecordMap.put("repair_date", repairDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[返修日期]，错误信息->[返修日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第33列的值 - 是否过三包
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            if ("是".equals(value)) {
                                saleRecordMap.put("whether_insure", "Y");
                            }else{
                                saleRecordMap.put("whether_insure", "N");
                            }
                        }

                        // 第34列的值 - 引导数据
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("guide_data", value);

                        // 第35列的值 - 吊牌与MES零件号对结果
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            if ("是".equals(value)) {
                                saleRecordMap.put("compare_mes_code", "Y");
                            }else{
                                saleRecordMap.put("compare_mes_code", "N");
                            }
                        }

                        // 第36列的值 - 是否无吊牌
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            if ("是".equals(value)) {
                                saleRecordMap.put("whether_tag", "Y");
                            }else{
                                saleRecordMap.put("whether_tag", "N");
                            }
                        }

                        // 第37列的值 - 失效零件
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("unwork_part", value);

                        // 第38列的值 - 故障件去向
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("defect_part_go", value);

                        // 第39列的值 - 申述记录
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("represent_record", value);

                        // 第40列的值 - 申述日期
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date appealDate = sdf.parse(value);
                                saleRecordMap.put("represent_time", appealDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[申述日期]，错误信息->[申述日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第41列的值 - 申述主机回复状态
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("represent_customer_reply_status", value);

                        // 第42列的值 - MES二维码ID
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("mes_qr_id", value);

                        // 第43列的值 - 申诉主机回复备注
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("represent_customer_reply_remark", value);

                        // 第44列的值 - 车辆生产日期
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                Date vehicleProductionDate = sdf.parse(value);
                                saleRecordMap.put("car_made_time", vehicleProductionDate);
                            } catch (ParseException e) {
                                errorInfos.add("第" + (i + 1) + "行导入失败，列[车辆生产日期]，错误信息->[车辆生产日期格式错误]<br/>");
                                continue;
                            }
                        }

                        // 第45列的值 - OCR文件相对路径
                        index++;
                        value = getCell(row, index);
                        saleRecordMap.put("ocrfile", value);

                        // 第46列的值 - 创建人（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列，无需处理

                        // 第47列的值 - 更新人（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列，无需处理

                        // 第48列的值 - 创建时间（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列，无需处理

                        // 第49列的值 - 更新时间（冗余列）
                        index++;
                        value = getCell(row, index);
                        // 冗余列，无需处理

                        // 第50列的值 - 是否同步到事件
                        index++;
                        value = getCell(row, index);
                        if (StringUtils.isNotBlank(value)) {
                            if ("是".equals(value)) {
                                saleRecordMap.put("if_sync", "Y");
                            }else{
                                saleRecordMap.put("if_sync", "N");
                            }
                        }

                        // 设置固定值
                        if (saleRecordMap.containsKey("id")) {

                            // 修改操作
                            saleRecordMap.put("update_user", BaseContextHandler.getUserID());
                            saleRecordMap.put("update_time", new Date());
                            saleRecordUpdateMap.put(i, saleRecordMap);
                        } else {
                            // 新增操作
                            saleRecordMap.put("create_user", BaseContextHandler.getUserID());
                            saleRecordMap.put("update_user", BaseContextHandler.getUserID());
                            saleRecordMap.put("create_time", new Date());
                            saleRecordMap.put("update_time", new Date());
                            saleRecordMap.put("opt_counter", 0L);
                            saleRecordMap.put("mark_for_delete", 0L);
                            saleRecordAddMap.put(i, saleRecordMap);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        // 记录具体哪一行发生了异常，并指明列名
                        String columnName = getColumnName(index);
                        errorInfos.add("第" + (i + 1) + "行处理失败，列[" + columnName + "]，错误信息->[" + e.getMessage() + "]<br/>");
                        continue;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    // 记录具体哪一行发生了异常
                    errorInfos.add("第" + (i + 1) + "行处理失败，错误信息->[" + e.getMessage() + "]<br/>");
                    continue;
                }
            }

            if(errorInfos.size() <= 0) {
                int succCount = 0;
                // 批量新增记录
                for (Map.Entry<Integer, Map<String, Object>> entry : saleRecordAddMap.entrySet()) {
                    try {
                        Map<String, Object> saleRecordMap = entry.getValue();
                        if (saleRecordMap != null) {
                            dynamicDataExecutor.save(saleRecordMap, saleRecordDefinition, saleRecordColumnProps);
                            succCount++;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        // 添加更详细的错误信息
                        String errorMsg = "新增第" + entry.getKey() + "行数据失败，错误信息->[" + e.getMessage() + "]<br/>";
                        // 显式抛出未检查的异常以触发事务回滚
                        throw new RuntimeException(errorMsg, e);
                        // errorInfos.add("新增第" + entry.getKey() + "行数据失败，错误信息->[" + e.getMessage() + "]<br/>");
                    }
                }

                // 批量修改记录
                for (Map.Entry<Integer, Map<String, Object>> entry : saleRecordUpdateMap.entrySet()) {
                    try {
                        Map<String, Object> saleRecordMap = entry.getValue();
                        if (saleRecordMap != null) {
                            dynamicDataExecutor.update(saleRecordMap, saleRecordColumnProps, saleRecordDefinition);
                            succCount++;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        // 添加更详细的错误信息
                        String errorMsg = "更新第" + entry.getKey() + "行数据失败，错误信息->[" + e.getMessage() + "]<br/>";
                        // 显式抛出未检查的异常以触发事务回滚
                        throw new RuntimeException(errorMsg, e);
                        // e.printStackTrace();
                        // errorInfos.add("更新第" + entry.getKey() + "行数据失败，错误信息->[" + e.getMessage() + "]<br/>");
                    }
                }

                // 如果在保存过程中出现了错误
                if(errorInfos.size() > 0) {
                    StringBuilder errorBuilder = new StringBuilder();
                    for (int i = 0; i < errorInfos.size(); i++) {
                        errorBuilder.append(errorInfos.get(i));
                    }
                    Map<String, Object> resultMap = new HashMap<>();
                    resultMap.put("totalCount", totalInt);
                    resultMap.put("hasError", true);
                    resultMap.put("errorInfos", errorBuilder.toString());
                    HashMap<String, Object> params = new HashMap<>();
                    params.put("returnData", resultMap);
                    return ServiceOutParam.success(params);
                }

                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("hasError",false);
                resultMap.put("totalCount", totalInt);
                resultMap.put("succCount", succCount);
                resultMap.put("addCount", saleRecordAddMap.size());
                resultMap.put("updateCount", saleRecordUpdateMap.size());
                HashMap<String, Object> params = new HashMap<>();
                params.put("returnData",resultMap);
                return ServiceOutParam.success(params);
            } else {
                StringBuilder errorBuilder = new StringBuilder();
                // errorBuilder.append("Excel导入失败，请检查以下错误：<br/>");
                for (int i = 0; i < errorInfos.size(); i++) {
                    errorBuilder.append(errorInfos.get(i));
                }
                Map<String, Object> resultMap = new HashMap<>();
                resultMap.put("totalCount", totalInt);
                resultMap.put("hasError",true);
                resultMap.put("errorInfos", errorBuilder.toString());
                HashMap<String, Object> params = new HashMap<>();
                params.put("returnData",resultMap);
                return ServiceOutParam.success(params);
            }
        } catch (Exception e) {
            e.printStackTrace();
            // 添加更详细的错误信息
            String errorMsg = "导入失败：" + (e.getMessage() != null ? e.getMessage() : "未知错误");
            // 如果有更深层次的异常，也一并输出
            if (e.getCause() != null) {
                errorMsg += "，原因：" + e.getCause().getMessage();
            }
            // String errorMsg = "更新第" + entry.getKey() + "行数据失败，错误信息->[" + e.getMessage() + "]<br/>";
            // 显式抛出未检查的异常以触发事务回滚
            throw new RuntimeException(errorMsg, e);

            // // 创建一个带有错误消息的返回对象
            // Map<String, Object> resultMap = new HashMap<>();
            // resultMap.put("hasError", true);
            // resultMap.put("errorInfos", errorMsg);
            // HashMap<String, Object> params = new HashMap<>();
            // params.put("returnData", resultMap);
            //
            // // 使用success返回而不是failed，以便前端能正确显示错误信息
            // return ServiceOutParam.success(params);
        }
    }

    /**
     * 获取主机厂信息
     */
    private Map<String,Map<String, Object>> getMainEnginePlantInfo(String system) {
        DynamicDataSourceHolder.setDbType(system);
        Map<String,Map<String, Object>> resultMap = new HashMap<>();
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT id, main_engine_plant_name FROM TM_MAIN_ENGINE_PLANT where mark_for_delete = 0 ");
        List<Map<String, Object>> mainEnginePlantList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        if(mainEnginePlantList != null && mainEnginePlantList.size() > 0){
            for(Map<String, Object> mainEnginePlant : mainEnginePlantList){
                String name = String.valueOf(mainEnginePlant.get("main_engine_plant_name"));
                resultMap.put(name, mainEnginePlant);
            }
        }
        return resultMap;
    }

    /**
     * 获取标准总成表信息
     */
    private Map<String,Map<String, Object>> getStandardAssemblyInfo(String system) {
        DynamicDataSourceHolder.setDbType(system);
        Map<String,Map<String, Object>> resultMap = new HashMap<>();
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT id, assembly_code FROM TB_STANDARD_ASSEMBLY_PRODUCT where mark_for_delete = 0 ");
        List<Map<String, Object>> standardAssemblyList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        if(standardAssemblyList != null && standardAssemblyList.size() > 0){
            for(Map<String, Object> standardAssembly : standardAssemblyList){
                String assemblyCode = String.valueOf(standardAssembly.get("assembly_code"));
                resultMap.put(assemblyCode, standardAssembly);
            }
        }
        return resultMap;
    }

    /**
     * 获取初步分析结果信息
     */
    private Map<String,Map<String, Object>> getMqpDefectInfo(String system) {
        DynamicDataSourceHolder.setDbType(system);
        Map<String,Map<String, Object>> resultMap = new HashMap<>();
        StringBuffer sql = new StringBuffer();
        sql.append("  SELECT id, first_analyse FROM TT_MQP_DEFECT where mark_for_delete = 0 ");
        List<Map<String, Object>> mqpDefectList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        if(mqpDefectList != null && mqpDefectList.size() > 0){
            for(Map<String, Object> mqpDefect : mqpDefectList){
                String firstAnalyse = String.valueOf(mqpDefect.get("first_analyse"));
                resultMap.put(firstAnalyse, mqpDefect);
            }
        }
        return resultMap;
    }

    /**
     * 获取Workbook
     */
    public XSSFWorkbook getWorkbook(InputStream ins, String fileName) throws Exception {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(ins);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 获取单元格的值
     */
    public static String getCell(Row row, int colIndex) {
        if (row == null) {
            return "";
        }
        try {
            Cell cell = row.getCell(colIndex);
            if (cell == null) {
                return "";
            }

            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    return NumberToTextConverter.toText(cell.getNumericCellValue());
                case Cell.CELL_TYPE_STRING:
                    return cell.getStringCellValue().trim();
                case Cell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue());
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception e) {
                        try {
                            return cell.getStringCellValue().trim();
                        } catch (Exception ex) {
                            return "";
                        }
                    }
                case Cell.CELL_TYPE_BLANK:
                    return "";
                default:
                    return "";
            }
        } catch (Exception e) {
            // 处理单元格获取值异常
            System.err.println("读取单元格[" + (row.getRowNum() + 1) + "行," + (colIndex + 1) + "列]时发生错误: " + e.getMessage());
            e.printStackTrace();
            return "";
        }
    }

    /**
     * 判断行是否为空
     */
    public static boolean isAllLineBlank(Row row, int judgeSize) {
        if (row == null) {
            return true;
        }
        // 实际列数可能会少于预期，防止越界访问
        int cellCount = Math.min(judgeSize, row.getLastCellNum());
        for (int i = 0; i < cellCount; i++) {
            String value = getCell(row, i);
            if (StringUtils.isNotBlank(value)) {
                return false;
            }
        }
        return true;
    }

    // 列名映射，用于错误提示
    private static String getColumnName(int index) {
        if (index < 0) {
            return "列" + (index + 1);
        }

        switch (index) {
            case 0: return "ID";
            case 1: return "编号";
            case 2: return "零件简称";
            case 3: return "产品名称";
            case 4: return "筛选时间";
            case 5: return "主机出库日期";
            case 6: return "生产基地";
            case 7: return "主机厂名称";
            case 8: return "车架号";
            case 9: return "行驶里程（KM）";
            case 10: return "批次号";
            case 11: return "灯具制造日期";
            case 12: return "用户描述（全字段）";
            case 13: return "用户简述";
            case 14: return "初步分析结果";
            case 15: return "缺陷代码";
            case 16: return "备注";
            case 17: return "最终分析结果";
            case 18: return "责任方";
            case 19: return "唯一号";
            case 20: return "换下零件号";
            case 21: return "换上零件号";
            case 22: return "产品编码";
            case 23: return "MES零件号编码";
            case 24: return "返修站代码";
            case 25: return "返修站名称";
            case 26: return "索赔申请号";
            case 27: return "委托书号";
            case 28: return "入库编号";
            case 29: return "索赔周次";
            case 30: return "购车日期";
            case 31: return "返修日期";
            case 32: return "是否过三包";
            case 33: return "引导数据";
            case 34: return "吊牌与MES零件号对结果";
            case 35: return "是否无吊牌";
            case 36: return "失效零件";
            case 37: return "故障件去向";
            case 38: return "申述记录";
            case 39: return "申述日期";
            case 40: return "申述主机回复状态";
            case 41: return "MES二维码ID";
            case 42: return "申诉主机回复备注";
            case 43: return "车辆生产日期";
            case 44: return "OCR文件相对路径";
            case 45: return "创建人";
            case 46: return "更新人";
            case 47: return "创建时间";
            case 48: return "更新时间";
            case 49: return "是否同步到事件";
            default: return "列" + (index + 1);
        }
    }

    private Map<String,String> getDictEntryByTypeCodes(String system,String typeCodes){
        DynamicDataSourceHolder.setDbType(system+"_admin");
        StringBuffer sql = new StringBuffer();
        sql.append(" select entry_code,entry_name from  ts_dict_entry where type_id in (select id from ts_dict_type where type_code in ("+typeCodes+")) ");
        List<Map<String, Object>> infoList = dynamicDataExecutor.findAllSql(sql);
        DynamicDataSourceHolder.clearDBType();
        Map<String,String> rtnMap = new HashMap<>();
        if(infoList != null && infoList.size() > 0){
            for(Map<String,Object> map : infoList){
                rtnMap.put(map.get("entry_code")+"",map.get("entry_name")+"");
            }
        }
        return rtnMap;
    }
}